
const pool = require('../config/db');

/**
 * @desc    获取所有老师
 * @route   GET /api/teachers
 * @access  Private/Admin
 */
const getTeachers = async (req, res) => {
    try {
    // 获取所有老师
    const [teachers] = await pool.query('SELECT * FROM teachers ORDER BY teacher_id');
    
    // 为每位老师获取教授的课程
    const teachersWithCourses = await Promise.all(
        teachers.map(async (teacher) => {
        const [courses] = await pool.query(`
            SELECT c.id, c.name 
            FROM courses c
            JOIN teacher_courses tc ON c.id = tc.course_id
            WHERE tc.teacher_id = ?
        `, [teacher.id]);
        
        return {
            ...teacher,
            courses: courses.map(course => course.name)
        };
        })
    );
    
    res.json(teachersWithCourses);
    } catch (error) {
    console.error('获取老师列表错误:', error);
    res.status(500).json({ message: '服务器错误' });
    }
};

/**
 * @desc    获取单个老师
 * @route   GET /api/teachers/:id
 * @access  Private/Admin
 */
const getTeacherById = async (req, res) => {
    try {
    // 获取老师信息
    const [teachers] = await pool.query(
        'SELECT * FROM teachers WHERE id = ?',
        [req.params.id]
    );

    if (teachers.length === 0) {
        return res.status(404).json({ message: '老师不存在' });
    }

    const teacher = teachers[0];
    
    // 获取老师教授的课程
    const [courses] = await pool.query(`
        SELECT c.id, c.name 
        FROM courses c
        JOIN teacher_courses tc ON c.id = tc.course_id
        WHERE tc.teacher_id = ?
    `, [teacher.id]);
    
    res.json({
        ...teacher,
        courses: courses.map(course => course.name)
    });
    } catch (error) {
    console.error('获取老师信息错误:', error);
    res.status(500).json({ message: '服务器错误' });
    }
};

/**
 * @desc    创建老师
 * @route   POST /api/teachers
 * @access  Private/Admin
 */
const createTeacher = async (req, res) => {
    const connection = await pool.getConnection();
    try {
    const {
        teacher_id,
        name,
        gender,
        phone,
        courses = [],
        is_head_teacher = false
    } = req.body;

    // 验证必填字段
    if (!teacher_id || !name || !gender || !phone) {
        return res.status(400).json({ message: '工号、姓名、性别和手机号为必填项' });
    }

    // 检查工号是否已存在
    const [existingTeachers] = await connection.query(
        'SELECT * FROM teachers WHERE teacher_id = ?',
        [teacher_id]
    );

    if (existingTeachers.length > 0) {
        connection.release();
        return res.status(400).json({ message: '该工号已存在' });
    }

    // 开始事务
    await connection.beginTransaction();

    // 创建老师
    const [result] = await connection.query(`
        INSERT INTO teachers 
        (teacher_id, name, gender, phone, is_head_teacher)
        VALUES (?, ?, ?, ?, ?)
    `, [
        teacher_id,
        name,
        gender,
        phone,
        is_head_teacher
    ]);

    const teacherId = result.insertId;

    // 处理课程
    if (courses.length > 0) {
        // 获取或创建课程
        for (const courseName of courses) {
        // 检查课程是否存在
        const [existingCourses] = await connection.query(
            'SELECT id FROM courses WHERE name = ?',
            [courseName]
        );

        let courseId;
        if (existingCourses.length > 0) {
            courseId = existingCourses[0].id;
        } else {
            // 创建新课程
            const [courseResult] = await connection.query(
            'INSERT INTO courses (name) VALUES (?)',
            [courseName]
            );
            courseId = courseResult.insertId;
        }

        // 关联老师和课程
        await connection.query(
            'INSERT INTO teacher_courses (teacher_id, course_id) VALUES (?, ?)',
            [teacherId, courseId]
        );
        }
    }

    // 提交事务
    await connection.commit();

    // 获取创建的老师及其课程
    const [newTeachers] = await connection.query(
        'SELECT * FROM teachers WHERE id = ?',
        [teacherId]
    );
    
    const [t_courses] = await connection.query(`
        SELECT c.name 
        FROM courses c
        JOIN teacher_courses tc ON c.id = tc.course_id
        WHERE tc.teacher_id = ?
    `, [teacherId]);

    connection.release();

    res.status(201).json({
        message: '老师创建成功',
        teacher: {
        ...newTeachers[0],
        courses: t_courses.map(course => course.name)
        }
    });
    } catch (error) {
    // 回滚事务
    await connection.rollback();
    connection.release();
    
    console.error('创建老师错误:', error);
    res.status(500).json({ message: '服务器错误' });
    }
};

/**
 * @desc    更新老师
 * @route   PUT /api/teachers/:id
 * @access  Private/Admin
 */
const updateTeacher = async (req, res) => {
    const connection = await pool.getConnection();
    
    try {
    const {
        teacher_id,
        name,
        gender,
        phone,
        courses = [],
        is_head_teacher = false
    } = req.body;

    // 检查老师是否存在
    const [teachers] = await connection.query(
        'SELECT * FROM teachers WHERE id = ?',
        [req.params.id]
    );

    if (teachers.length === 0) {
        connection.release();
        return res.status(404).json({ message: '老师不存在' });
    }

    // 验证必填字段
    if (!teacher_id || !name || !gender || !phone) {
        connection.release();
        return res.status(400).json({ message: '工号、姓名、性别和手机号为必填项' });
    }

    // 检查工号是否已被其他老师使用
    if (teacher_id !== teachers[0].teacher_id) {
        const [existingTeachers] = await connection.query(
        'SELECT * FROM teachers WHERE teacher_id = ? AND id != ?',
        [teacher_id, req.params.id]
        );

        if (existingTeachers.length > 0) {
        connection.release();
        return res.status(400).json({ message: '该工号已存在' });
        }
    }

    // 开始事务
    await connection.beginTransaction();

    // 更新老师信息
    await connection.query(`
        UPDATE teachers SET
        teacher_id = ?,
        name = ?,
        gender = ?,
        phone = ?,
        is_head_teacher = ?
        WHERE id = ?
    `, [
        teacher_id,
        name,
        gender,
        phone,
        is_head_teacher,
        req.params.id
    ]);

    // 先删除现有课程关联
    await connection.query(
        'DELETE FROM teacher_courses WHERE teacher_id = ?',
        [req.params.id]
    );

    // 处理新课程关联
    if (courses.length > 0) {
        for (const courseName of courses) {
        // 检查课程是否存在
        const [existingCourses] = await connection.query(
            'SELECT id FROM courses WHERE name = ?',
            [courseName]
        );

        let courseId;
        if (existingCourses.length > 0) {
            courseId = existingCourses[0].id;
        } else {
            // 创建新课程
            const [courseResult] = await connection.query(
            'INSERT INTO courses (name) VALUES (?)',
            [courseName]
            );
            courseId = courseResult.insertId;
        }

        // 关联老师和课程
        await connection.query(
            'INSERT INTO teacher_courses (teacher_id, course_id) VALUES (?, ?)',
            [req.params.id, courseId]
        );
        }
    }

    // 提交事务
    await connection.commit();

    // 获取更新后的老师及其课程
    const [updatedTeachers] = await connection.query(
        'SELECT * FROM teachers WHERE id = ?',
        [req.params.id]
    );
    
    const [t_courses] = await connection.query(`
        SELECT c.name 
        FROM courses c
        JOIN teacher_courses tc ON c.id = tc.course_id
        WHERE tc.teacher_id = ?
    `, [req.params.id]);

    connection.release();

    res.json({
        message: '老师更新成功',
        teacher: {
        ...updatedTeachers[0],
        courses: t_courses.map(course => course.name)
        }
    });
    } catch (error) {
    // 回滚事务
    await connection.rollback();
    connection.release();
    
    console.error('更新老师错误:', error);
    res.status(500).json({ message: '服务器错误' });
    }
};

/**
 * @desc    删除老师
 * @route   DELETE /api/teachers/:id
 * @access  Private/Admin
 */
const deleteTeacher = async (req, res) => {
    const connection = await pool.getConnection();
    
    try {
    // 检查老师是否存在
    const [teachers] = await connection.query(
        'SELECT * FROM teachers WHERE id = ?',
        [req.params.id]
    );

    if (teachers.length === 0) {
        connection.release();
        return res.status(404).json({ message: '老师不存在' });
    }

    // 开始事务
    await connection.beginTransaction();

    // 删除课程关联
    await connection.query(
        'DELETE FROM teacher_courses WHERE teacher_id = ?',
        [req.params.id]
    );

    // 更新班级表中的班主任信息
    await connection.query(
        'UPDATE classes SET head_teacher_id = NULL WHERE head_teacher_id = ?',
        [req.params.id]
    );

    // 删除老师
    await connection.query(
        'DELETE FROM teachers WHERE id = ?',
        [req.params.id]
    );

    // 提交事务
    await connection.commit();
    connection.release();

    res.json({ message: '老师删除成功' });
    } catch (error) {
    // 回滚事务
    await connection.rollback();
    connection.release();
    
    console.error('删除老师错误:', error);
    res.status(500).json({ message: '服务器错误' });
    }
};

module.exports = {
    getTeachers,
    getTeacherById,
    createTeacher,
    updateTeacher,
    deleteTeacher
};

